﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SF.DAL
{
    public class statistics
    {
        /// <summary>
        /// 获取服务号总营业额
        /// </summary>
        /// <param name="businessNum"></param>
        /// <returns></returns>
        public decimal GetAppNumTotalMoney(string appNum) 
        {
            string sql = "SELECT SUM(totalMoney) total FROM [sf_goods_order] where appNum = '" + appNum + "' and ispay = 2";

            object obj = DBUtility.DbHelperSQL.GetSingle(sql);
            if (obj != null)
            {
                return decimal.Parse(obj.ToString());
            }
            else 
            {
                return 0;
            }
        }

        /// <summary>
        /// 获取服务号订单总数
        /// </summary>
        /// <param name="appNum"></param>
        /// <returns></returns>
        public int GetAppNumOrdersCount(string appNum) 
        {
            string sql = "SELECT count(id) total FROM [sf_goods_order] where appNum = '" + appNum + "'";
            object obj = DBUtility.DbHelperSQL.GetSingle(sql);
            if (obj != null)
            {
                return int.Parse(obj.ToString());
            }
            else 
            {
                return 0;
            }
        }

        /// <summary>
        /// 获取服务号客户总数
        /// </summary>
        /// <param name="appNum"></param>
        /// <returns></returns>
        public int GetAppNumCustomersCount(string appNum) 
        {
            string sql = "SELECT COUNT(id) FROM [sf_user_info] where appNum = '" + appNum + "'";
            object obj = DBUtility.DbHelperSQL.GetSingle(sql);
            if (obj != null)
            {
                return int.Parse(obj.ToString());
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 获取指定商户服务号个数
        /// </summary>
        /// <param name="businessNum"></param>
        /// <returns></returns>
        public int GetBusinessAppIdCount(string businessNum) 
        {
            string sql = "SELECT COUNT(id) FROM [sf_appidmanage] where businessNum = '" + businessNum + "'";
            object obj = DBUtility.DbHelperSQL.GetSingle(sql);
            if (obj != null)
            {
                return int.Parse(obj.ToString());
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 获取服务号当天消息总数
        /// </summary>
        /// <param name="accept_user_name"></param>
        /// <returns></returns>
        public int GetAppNumTodayNoticesCount(string accept_user_name) 
        {
            string sql = "select count(id) from sf_manager_notices where accept_user_name = '" + accept_user_name + "' and is_read = 0 and SUBSTRING(CONVERT(varchar(50),post_time,120 ),0,12) = '"+DateTime.Today.ToString("yyyy-MM-dd")+"'";
            object obj = DBUtility.DbHelperSQL.GetSingle(sql);
            if (obj != null)
            {
                return int.Parse(obj.ToString());
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 获取服务号当天订单总数
        /// </summary>
        /// <param name="appNum"></param>
        /// <returns></returns>
        public int GetAppNumTodayOrdersCount(string appNum) 
        {
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql = "SELECT count(id) total FROM [sf_goods_order] where appNum = '" + appNum + "' and SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,11) = '" + today + "'";
            object obj = DBUtility.DbHelperSQL.GetSingle(sql);
            if (obj != null)
            {
                return int.Parse(obj.ToString());
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 获取服务号当天客户总数
        /// </summary>
        /// <param name="appNum"></param>
        /// <returns></returns>
        public int GetAppNumTodayCustomersCount(string appNum) 
        {
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql = "SELECT count(id) total FROM [sf_user_info]  where appNum = '" + appNum + "' and SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,11) = '" + today + "'";
            object obj = DBUtility.DbHelperSQL.GetSingle(sql);
            if (obj != null)
            {
                return int.Parse(obj.ToString());
            }
            else
            {
                return 0;
            }
        }


        /// <summary>
        /// 获取商户一段时间内的订单统计信息（公众服务号,订购时间，数量，总金额）
        /// </summary>
        /// <returns></returns>
        public System.Data.DataSet GetOrderTimesDataByBusinessNum(string businessNum, string startTime, string endTime)
        {
            string sql = string.Format(@"SELECT appNum, SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,11) day
		                    ,COUNT([id]) count
		                    ,sum([totalMoney]) totalmoney
                          from sf_goods_order     
                      where busnieseNum = '{0}' and ispay = 2 and addTime >= '{1}' and addTime <= '{2}' 
                      group by appNum,SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,11)
                      order by SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,11)", businessNum, startTime, endTime);

            return DBUtility.DbHelperSQL.Query(sql);
        }

        /// <summary>
        /// 获取指定公众服务号下的客户来源
        /// </summary>
        /// <param name="appNum"></param>
        /// <returns></returns>
        public System.Data.DataSet GetCustomerSourceByAppNum(string appNum)
        {
            string sql = string.Format(@"SELECT city,COUNT(id) count
                                  FROM [sf_user_info]
                                  where appNum = '{0}'
                                  group by city", appNum);

            return DBUtility.DbHelperSQL.Query(sql);
        }
        /// <summary>
        /// 获取指定公众服务号下的客户来源
        /// 官方使用
        /// </summary>
        public System.Data.DataSet GetCustomerSourceByAppNum()
        {
            string sql = string.Format(@"SELECT city,COUNT(id) count
                                  FROM [sf_user_info]
                                  group by city");

            return DBUtility.DbHelperSQL.Query(sql);
        }

        /// <summary>
        /// 获取指定商户客户来源数据(group by city)
        /// </summary>
        /// <param name="businessNum"></param>
        /// <returns></returns>
        public System.Data.DataSet GetCustomerSourceByBusinessNum(string businessNum)
        {
            string sql = string.Format(@"SELECT city,COUNT(id) count
                                  FROM [sf_user_info]
                                  where busnieseNum = '{0}'
                                  group by city", businessNum);

            return DBUtility.DbHelperSQL.Query(sql);
        }

        /// <summary>
        /// 获取指定公众号订单报表统计数据
        /// </summary>
        /// <param name="appNum"></param>
        /// <returns></returns>
        public System.Data.DataSet GetOrderReportByAppNum(string appNum)
        {
            string sql = string.Format(@"select COUNT(o.userNum) usercount,SUM(o.count) ordercount,SUM(o.totalmoney) totalmoney,u.city from (
                                            SELECT userNum,count([id]) count
                                                  ,sum([totalMoney]) totalmoney
                                              FROM [sf_goods_order]
                                              Where appNum = '{0}' and isPay = 2
                                              group by userNum ) o
                                             left join [sf_user_info] u on o.userNum = u.userNum
                                             group by city
                                             order by city", appNum);

            return DBUtility.DbHelperSQL.Query(sql);
        }
        /// <summary>
        /// 获取所有订单报表统计数据
        /// 官方管理使用
        /// </summary>
        /// <param name="appNum"></param>
        /// <returns></returns>
        public System.Data.DataSet GetOrderReportByAppNum()
        {
            string sql = string.Format(@"select COUNT(o.userNum) usercount,SUM(o.count) ordercount,SUM(o.totalmoney) totalmoney,u.city from (
                                            SELECT userNum,count([id]) count
                                                  ,sum([totalMoney]) totalmoney
                                              FROM [sf_goods_order]
                                              where isPay = 2
                                              group by userNum ) o
                                             left join [sf_user_info] u on o.userNum = u.userNum
                                             group by city
                                             order by city");

            return DBUtility.DbHelperSQL.Query(sql);
        }

        /// <summary>
        /// 获取指定商户订单报表统计数据
        /// </summary>
        /// <param name="appNum"></param>
        /// <returns></returns>
        public System.Data.DataSet GetOrderReportByBusinessNum(string businessNum) 
        {
            string sql = string.Format(@"select COUNT(o.userNum) usercount,SUM(o.count) ordercount,SUM(o.totalmoney) totalmoney,u.city from (
                                            SELECT userNum,count([id]) count
                                                  ,sum([totalMoney]) totalmoney
                                              FROM [sf_goods_order]
                                              Where busnieseNum = '{0}' and ispay = 2
                                              group by userNum ) o
                                             left join [sf_user_info] u on o.userNum = u.userNum
                                             group by city
                                             order by city", businessNum);

            return DBUtility.DbHelperSQL.Query(sql);
        }


        /// <summary>
        /// 通过公众服务号获取客户一段时间内的数据
        /// </summary>
        /// <param name="businessNum"></param>
        /// <param name="strStartTime"></param>
        /// <param name="strEndTime"></param>
        /// <returns></returns>
        public System.Data.DataSet GetCustomerTimesDataByAppNum(string appNum, string startTime, string endTime)
        {
            string sql = string.Format(@"SELECT count([id]) count,city,SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,11) day
                              FROM [sf_user_info]
                              where appNum = '{0}' and addTime >= '{1}' and addTime <= '{2}' 
                              group by city,SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,11)", appNum, startTime, endTime);

            return DBUtility.DbHelperSQL.Query(sql);
        }
        /// <summary>
        /// 通过公众服务号获取客户一段时间内的数据
        /// 官方使用
        /// </summary>
        public System.Data.DataSet GetCustomerTimesDataByAppNum(string startTime, string endTime)
        {
            string sql = string.Format(@"SELECT count([id]) count,city,SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,11) day
                              FROM [sf_user_info]
                              where addTime >= '{0}' and addTime <= '{1}' 
                              group by city,SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,11)", startTime, endTime);

            return DBUtility.DbHelperSQL.Query(sql);
        }

        /// <summary>
        /// 通过商户号获取一段时间内的客户数据
        /// </summary>
        /// <param name="businessNum"></param>
        /// <param name="selTimes"></param>
        /// <param name="strStartTime"></param>
        /// <param name="strEndTime"></param>
        /// <returns></returns>
        public System.Data.DataSet GetCustomerTimesDataByBusinessNum(string businessNum,string strStartTime , string strEndTime)
        {
            string sql = string.Format(@" SELECT count([id]) count,appNum,SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,11) day
                                                      FROM [sf_user_info]
                                                      where busnieseNum = '{0}' and addTime >= '{1}' and addTime <= '{2}' 
                                                      group by appNum,SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,11)", businessNum, strStartTime, strEndTime);

            return DBUtility.DbHelperSQL.Query(sql);
        }

        /// <summary>
        /// 通过公众号获取一段时间内订单走势（年度-月份）
        /// </summary>
        /// <param name="businessNum"></param>
        /// <param name="strStartTime"></param>
        /// <param name="strEndTime"></param>
        /// <returns></returns>
        public System.Data.DataSet GetOrderTrendTimesDataByAppNum(string appNum, string strStartTime, string strEndTime)
        {
            string sql = string.Format(@"SELECT SUBSTRING(CONVERT(varchar(50),addTime,120 ),3,5) day
		                    ,COUNT([id]) count
		                    ,sum([totalMoney]) totalmoney
                          from sf_goods_order     
                      where appNum = '{0}' and ispay = 2 and 
                      SUBSTRING(CONVERT(varchar(50),addTime,120 ),3,5) >= '{1}' and 
                      SUBSTRING(CONVERT(varchar(50),addTime,120 ),3,5) <= '{2}' 
                      group by SUBSTRING(CONVERT(varchar(50),addTime,120 ),3,5)", appNum, strStartTime, strEndTime);

            return DBUtility.DbHelperSQL.Query(sql);
        }
        /// <summary>
        /// 通过公众号获取一段时间内订单走势（年度-月份）
        /// </summary>
        public System.Data.DataSet GetOrderTrendTimesDataByAppNum(string strStartTime, string strEndTime)
        {
            string sql = string.Format(@"SELECT SUBSTRING(CONVERT(varchar(50),addTime,120 ),3,5) day
		                    ,COUNT([id]) count
		                    ,sum([totalMoney]) totalmoney
                          from sf_goods_order     
                      where  
                      ispay = 2 and
                      SUBSTRING(CONVERT(varchar(50),addTime,120 ),3,5) >= '{0}' and 
                      SUBSTRING(CONVERT(varchar(50),addTime,120 ),3,5) <= '{1}' 
                      group by SUBSTRING(CONVERT(varchar(50),addTime,120 ),3,5)", strStartTime, strEndTime);

            return DBUtility.DbHelperSQL.Query(sql);
        }

        /// <summary>
        /// 通过商户编号获取一段时间内订单走势（年度-月份）
        /// </summary>
        /// <param name="businessNum"></param>
        /// <param name="strStartTime"></param>
        /// <param name="strEndTime"></param>
        /// <returns></returns>
        public System.Data.DataSet GetOrderTrendTimesDataByBusinessNum(string businessNum, string strStartTime, string strEndTime) 
        {
            string sql = string.Format(@"SELECT appNum, SUBSTRING(CONVERT(varchar(50),addTime,120 ),3,5) day
		                    ,COUNT([id]) count
		                    ,sum([totalMoney]) totalmoney
                          from sf_goods_order     
                      where busnieseNum = '{0}' and ispay = 2 and 
                      SUBSTRING(CONVERT(varchar(50),addTime,120 ),3,5) >= '{1}' and 
                      SUBSTRING(CONVERT(varchar(50),addTime,120 ),3,5) <= '{2}' 
                      group by appNum,SUBSTRING(CONVERT(varchar(50),addTime,120 ),3,5)", businessNum,strStartTime,strEndTime);

            return DBUtility.DbHelperSQL.Query(sql);
        }

        /// <summary>
        /// 获取客户来源转换率
        /// </summary>
        /// <param name="businessNum"></param>
        /// <returns></returns>
        public System.Data.DataSet GetCustomerSourceRatesByBusinessNum(string businessNum) 
        {
            string sql = string.Format(@"select appNum,rate,count(rate) rates from (
                        select t1.userNum,t1.appNum,case when t2.count > 0 then '购买' else '未购买' end rate from (SELECT [userNum]
                              ,appNum
                          FROM [sf_user_info]
                            where busnieseNum = '{0}'                        
                            ) t1 left join 
                        (
                        SELECT [usernum],count([id]) count
                          FROM [sf_goods_order]
                          where busnieseNum = '{0}' and ispay = 2
                          group by userNum) t2 on t1.userNum = t2.userNum
                          ) tt group by appNum,rate
                          order by appNum,rate", businessNum);

            return DBUtility.DbHelperSQL.Query(sql);
        }


        /// <summary>
        /// 获取指定公众号的环比数据
        /// </summary>
        /// <param name="appNum"></param>
        /// <param name="strStartTime"></param>
        /// <param name="strEndTime"></param>
        /// <returns></returns>
        public System.Data.DataSet GetQuarterDataByAppNum(string appNum, string strStartTime, string strEndTime)
        {
            string sql = string.Format(@"SELECT SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,8) day
		                    ,COUNT([id]) count
                          from sf_goods_order     
                      where appNum = '{0}' and ispay = 2 and 
                      SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,5) >= '{1}' and 
                      SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,5) <= '{2}' 
                      group by SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,8)", appNum, strStartTime, strEndTime);

            return DBUtility.DbHelperSQL.Query(sql);
        }
        /// <summary>
        /// 获取指定公众号的环比数据
        /// 官方使用
        /// </summary>
        public System.Data.DataSet GetQuarterDataByAppNum(string strStartTime, string strEndTime)
        {
            string sql = string.Format(@"SELECT SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,8) day
		                    ,COUNT([id]) count
                          from sf_goods_order     
                      where ispay = 2 and
                      SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,5) >= '{0}' and 
                      SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,5) <= '{1}' 
                      group by SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,8)", strStartTime, strEndTime);

            return DBUtility.DbHelperSQL.Query(sql);
        }

        /// <summary>
        /// 获取指定商户的环比数据
        /// </summary>
        /// <param name="appNum"></param>
        /// <param name="strStartTime"></param>
        /// <param name="strEndTime"></param>
        /// <returns></returns>
        public System.Data.DataSet GetQuarterDataByBusinessNum(string businessNum, string strStartTime, string strEndTime)
        {
            string sql = string.Format(@"SELECT SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,8) day
		                    ,COUNT([id]) count
                          from sf_goods_order     
                      where busnieseNum = '{0}' and ispay = 2 and 
                      SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,5) >= '{1}' and 
                      SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,5) <= '{2}' 
                      group by SUBSTRING(CONVERT(varchar(50),addTime,120 ),0,8)", businessNum, strStartTime, strEndTime);

            return DBUtility.DbHelperSQL.Query(sql);
        }
    }
}
